package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import model.Model;
import budget.Budget;
import budget.Service;
import budget.Service.ServiceType;
import controllers.Checks;
import controllers.ExceptionsTypes;
import controllers.FieldErrors;

public class ServiceWrapper {

	public static ArrayList<Service> getBudgetServices(long idBudget, boolean closeConnection) throws Exception{
		ArrayList<Service> services = new ArrayList<Service>();
	    String query = "SELECT id_service "  +
	    		"FROM "+ DBWrapper.getDbname() +".list_of_services " +
	    		"WHERE id_budget = "+idBudget;
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while(rs.next()){
	        	services.add(ServiceWrapper.getService(rs.getInt("id_service"), false));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    return services;
	}
	
	public static ArrayList<Service> getServices(Budget b, boolean closeConnection) throws Exception{
		ArrayList<Service> listOfServices = new ArrayList<Service>();
		  String query = "SELECT * "  +
		    		"FROM "+ DBWrapper.getDbname() +".list_of_services " +
		    		"WHERE id_budget= "+ b.getId();
		  	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while(rs.next()){
	        	listOfServices.add(getService(rs.getInt("id_service"), false));
	        }
		return listOfServices;
	}
	
	public static Service getService(long idService, boolean closeConnection) throws Exception{
		Service s = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".service " +
	    		"WHERE id_service = "+idService;
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	String serviceType = rs.getString("type_of_service");
	        	ServiceType st = ServiceType.valueOf(serviceType);
	        	double cost = rs.getDouble("cost");
	        	String name = rs.getString("name");
	        	boolean onlyOne = rs.getBoolean("only_one");
	        	boolean atDay = rs.getBoolean("at_day");
	        	boolean atPerson = rs.getBoolean("at_person");
	        	s = new Service(idService, st, name, cost, onlyOne, atPerson, atDay);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	    if(s == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return s;
	}
	
	public static Model getService(String name, ServiceType serviceType, boolean closeConnection) throws Exception {
		Service s = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".service " +
	    		"WHERE name='"+ name +"'" + " AND type_of_service='" + serviceType.name() +"'";
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	long idService = rs.getInt("id_service");
	        	double cost = rs.getDouble("cost");
	        	boolean onlyOne = rs.getBoolean("only_one");
	        	boolean atDay = rs.getBoolean("at_day");
	        	boolean atPerson = rs.getBoolean("at_person");
	        	s = new Service(idService, serviceType, name, cost, onlyOne, atPerson, atDay);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	    if(s == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return s;
	}
	
	public static void insertService(boolean closeConnection, Service ...services) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<services.length; i++){
			Service s = services[i];
			int rows = stmt.executeUpdate(
		        	"INSERT INTO "+ DBWrapper.getDbname() +".service" +
		        	"(type_of_service, name, cost, only_one, at_day, at_person) " +
		            "VALUES('" + s.getServiceType().name()+ "', '" + s.getName() + "', " + s.getCost()+ ", " + s.isOnlyOne()+ ", " + s.isAtDay() +", " + s.isPerPerson() + ")");
			if(rows == 0)
				throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
			s.setId(getService(s.getName(), s.getServiceType(), false).getId());
		}
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
	public static void insertListOfServices(Budget budget, boolean closeConnection) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		
		Collection<Service> listOfService = budget.getServices();
		Collection<Service> oldListOfServices = ServiceWrapper.getBudgetServices(budget.getId(), closeConnection);
		if(!oldListOfServices.isEmpty()){
		int delete = stmt.executeUpdate("DELETE "  +
    			"FROM "+ DBWrapper.getDbname() +".list_of_services " +
    			"WHERE id_budget="+budget.getId()+"");
		if(delete == 0)
			throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		}
	
			for(Service sc : listOfService){
				ArrayList<Long> ids = ServiceWrapper.getListOfServices(budget, false);
				if(!ids.contains(sc.getId())){
					int rows = stmt.executeUpdate(
							"INSERT INTO "+ DBWrapper.getDbname() +".list_of_services " +
							"VALUES(" + budget.getId()+ ", " + sc.getId() + ")");
					if(rows == 0)
						throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
				}
//				else
//					throw new Exception(FieldErrors.LIST_OF_SERVICES_INVALID.name());
			}
		stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
	public static ArrayList<Long> getListOfServices(Budget budget, boolean closeConnection) throws Exception {
	
		ArrayList<Long> servicesIds = new ArrayList<Long>();
		 String query = "SELECT * "  +
		    		"FROM "+ DBWrapper.getDbname() +".list_of_services " +
		    		"WHERE id_budget=" + budget.getId();
		   
		    	Statement stmt = DBWrapper.getConnection().createStatement();
		        ResultSet rs = stmt.executeQuery(query);
		        while (rs.next()) {
		        	long idService = rs.getInt("id_service");
		        	servicesIds.add(idService);
		        }
		        stmt.close();
		        if(closeConnection)
		        	DBWrapper.getConnection().close();
		    return servicesIds;
	}
	
	public static ArrayList<Service> getAllServices(boolean closeConnection) throws Exception{
		
		ArrayList<Service> services = new ArrayList<Service>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".service ";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	long idService = rs.getInt("id_service");
	        	String sType = rs.getString("type_of_service");
	        	String name = rs.getString("name");
	        	double cost = rs.getDouble("cost");
	        	boolean onlyOne = rs.getBoolean("only_one");
	        	boolean atDay = rs.getBoolean("at_day");
	        	boolean atPerson = rs.getBoolean("at_person");
	        	services.add(new Service(idService, ServiceType.valueOf(sType) , name, cost, onlyOne, atPerson, atDay));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	    return services;
	}
	
	public static void modifyService(Service serv, boolean closeConnection) throws SQLException, Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		
		int rows = stmt.executeUpdate(
		        "UPDATE "+ DBWrapper.getDbname() +".service" +
		        " SET type_of_service='" + serv.getServiceType().toString() +
		        "', name='" + serv.getName() +
		        "', cost="+  serv.getCost() +
		        ", only_one="+ serv.isOnlyOne() +
		        ", at_day="+ serv.isAtDay() +
		        ", at_person="+ serv.isPerPerson() +
		        " WHERE id_service=" + serv.getId());
		
		/** Esegue l'update di tutti i Budget che contengono quei Servizi */
		
		ArrayList<Budget> bud = BudgetWrapper.getAllBudget(false);
		
		for(Budget b : bud){
			Collection<Service> listOfServices = b.getServices();
				for(Service s : listOfServices)
					if(s.getId() == serv.getId()){
						b.calculateTotalCostWhitoutAdjustment();
						Checks.checkBudget(b);
						BudgetWrapper.modifyBudget(false, b);
					}
		}
			
		if(rows == 0)
			throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
        if(closeConnection)
        	DBWrapper.getConnection().close();
        
	}
	
	public static void removeService(Service serv, boolean closeConnection) throws SQLException, Exception{
		try { 
		String query = "DELETE "  +
		    			"FROM "+ DBWrapper.getDbname() +".service " +
		    			"WHERE id_service="+serv.getId()+"";
		    
		    	Statement stmt = DBWrapper.getConnection().createStatement();
		        int rows = stmt.executeUpdate(query);
		        if(rows>1){
		        	throw new SQLException();
		        }else if(rows == 0)
		    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		        stmt.close();
		} catch (SQLException e){ 
			throw new Exception(FieldErrors.SERVICE_STILL_IN_BUDGET.name());
		}
		        if(closeConnection)
		        	DBWrapper.getConnection().close();
	}
}
